package com.unlcn.ils.wms.backend.service.stock.impl;

import cn.huiyunche.commons.domain.PageVo;
import cn.huiyunche.commons.domain.ResultDTOWithPagination;
import cn.huiyunche.commons.exception.BusinessException;
import cn.huiyunche.commons.utils.HttpRequestUtil;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Maps;
import com.unlcn.ils.wms.backend.bo.stockBO.WmsVehicleTrackingBO;
import com.unlcn.ils.wms.backend.enums.*;
import com.unlcn.ils.wms.backend.service.inspectApp.WmsOutboundComponentMissService;
import com.unlcn.ils.wms.backend.service.stock.WmsVehicleTrackingService;
import com.unlcn.ils.wms.backend.util.BrowerEncodeingUtils;
import com.unlcn.ils.wms.backend.util.DateUtils;
import com.unlcn.ils.wms.base.dto.WmsSelectExcpDetailsByVinDTO;
import com.unlcn.ils.wms.base.dto.WmsVehicleTrackingDTO;
import com.unlcn.ils.wms.base.dto.WmsVehicleTrackingExcpDTO;
import com.unlcn.ils.wms.base.mapper.extmapper.WmsInboundOrderDetailExtMapper;
import com.unlcn.ils.wms.base.mapper.extmapper.WmsVehicleTrackingExtMapper;
import com.unlcn.ils.wms.base.mapper.inspectApp.WmsOutboundInspectExpandMapper;
import com.unlcn.ils.wms.base.mapper.outbound.WmsOutboundTaskMapper;
import com.unlcn.ils.wms.base.mapper.stock.WmsVehicleTrackingLogMapper;
import com.unlcn.ils.wms.base.mapper.stock.WmsVehicleTrackingMapper;
import com.unlcn.ils.wms.base.model.outbound.WmsOutboundTask;
import com.unlcn.ils.wms.base.model.outbound.WmsOutboundTaskExample;
import com.unlcn.ils.wms.base.model.stock.WmsInventoryLocation;
import com.unlcn.ils.wms.base.model.stock.WmsVehicleTrackingExample;
import com.unlcn.ils.wms.base.model.stock.WmsVehicleTrackingLogWithBLOBs;
import com.unlcn.ils.wms.base.model.stock.WmsVehicleTrackingWithBLOBs;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

/**
 * 入库跟踪业务
 */
@Service
public class WmsVehicleTrackingServiceImpl implements WmsVehicleTrackingService {

    private Logger logger = LoggerFactory.getLogger(WmsVehicleTrackingServiceImpl.class);

    @Autowired
    private WmsVehicleTrackingMapper wmsVehicleTrackingMapper;
    @Autowired
    private WmsInboundOrderDetailExtMapper WmsInboundOrderDetailExtMapper;
    @Autowired
    private WmsOutboundTaskMapper wmsOutboundTaskMapper;
    @Autowired
    private WmsVehicleTrackingExtMapper wmsVehicleTrackingExtMapper;
    @Autowired
    private WmsOutboundInspectExpandMapper wmsOutboundInspectExpandMapper;
    @Autowired
    private WmsOutboundComponentMissService wmsOutboundComponentMissService;

    @Value("${tms.pickup.host.url}")
    private String propertyUrl;

    @Value("${wms.pickup.host.url}")
    private String wmsApiUrl;

    @Value("${tms.pickup.host.timeout}")
    private String propertyTime;

    @Value("${tms.encode.key}")
    private String propertyKey;


    /**
     * 重庆库业务--跟踪业务流程
     *
     * @param bo     参数封装
     * @param whCode 仓库code
     * @param userId @throws Exception
     */
    @Override
    public ResultDTOWithPagination<List<WmsVehicleTrackingWithBLOBs>> updateTrackingList(WmsVehicleTrackingBO bo, String whCode, String userId) throws Exception {
        logger.info("WmsVehicleTrackingServiceImpl.updateTrackingList paramTracking{},{},{},{}", bo, whCode, userId);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat tms_request = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat tms_response = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        if (StringUtils.isBlank(whCode)) {
            throw new BusinessException("仓库code为空!");
        }
        if (!WhCodeEnum.UNLCN_XN_CQ.getValue().equals(whCode)) {
            throw new BusinessException("该仓库不支持该操作!");
        }
        if (StringUtils.isBlank(userId)) {
            throw new BusinessException("用户Id不能为空!");
        }
        PageVo pageVo = new PageVo();
        if (StringUtils.isNotBlank(bo.getPageNo())) {
            pageVo.setPageNo(Integer.valueOf(bo.getPageNo()));
        }
        if (StringUtils.isNotBlank(bo.getPageSize())) {
            pageVo.setPageSize(Integer.valueOf(bo.getPageSize()));
        }

        if (StringUtils.isBlank(bo.getVin())
                && StringUtils.isBlank(bo.getOrderno())
                && StringUtils.isBlank(bo.getInboundBegin())
                && StringUtils.isBlank(bo.getInboundEnd())
                && StringUtils.isBlank(bo.getOrderEnd())
                && StringUtils.isBlank(bo.getOrderBegin())) {
            throw new BusinessException("订单号、车架号、下单时间、入库时间，至少填写一项!");
        }

        //校验订单时间和格式
        Date orderBegin = null;
        Date orderEnd = null;
        if (StringUtils.isNotBlank(bo.getOrderBegin())) {
            try {
                orderBegin = sdf.parse(bo.getOrderBegin());
            } catch (ParseException e) {
                throw new BusinessException("订单起始时间格式不正确(yyyy-MM-dd HH:mm:ss)");
            }
        }
        if (StringUtils.isNotBlank(bo.getOrderEnd())) {
            try {
                orderEnd = sdf.parse(bo.getOrderEnd());
                //校验只能查询下单时间间隔一个月的数据；
                if (orderBegin != null && orderEnd != null) {
                    Calendar start = Calendar.getInstance();
                    Calendar after = Calendar.getInstance();
                    start.setTime(orderBegin);
                    after.setTime(orderEnd);
                    int mo = after.get(Calendar.MONTH) - start.get(Calendar.MONTH);
                    int ye = (after.get(Calendar.YEAR) - start.get(Calendar.YEAR)) * 12;
                    int result = Math.abs(mo + ye);
                    if (result > 1) {
                        throw new BusinessException("只能查询下单时间间隔一个月内的数据");
                    }
                }
            } catch (ParseException e) {
                throw new BusinessException("订单起始时间格式不正确(yyyy-MM-dd HH:mm:ss)");
            }
        }

        byte del = DeleteFlagEnum.DELETED.getValue();
        //1.先查询本地介于入库时间开始和入库
        Date inboundBegin = null;
        if (StringUtils.isNotBlank(bo.getInboundBegin())) {
            inboundBegin = sdf.parse(bo.getInboundBegin());
        }
        Date inboundEnd = null;
        if (StringUtils.isNotBlank(bo.getInboundEnd())) {
            inboundEnd = sdf.parse(bo.getInboundEnd());
        }
        //下单时间不为空--入库时间--车架号--订单号
        //使用map拼接条件
        StringBuilder sb = null;
        HashMap<String, Object> param = Maps.newHashMap();
        param.put("vin", bo.getVin());
        param.put("inboundBegin", inboundBegin);
        param.put("inboundEnd", inboundEnd);
        param.put("orderno", bo.getOrderno());
        param.put("orderByCause", StringUtils.isBlank(pageVo.getOrder()) ? " a.gmt_create desc " : pageVo.getOrder());
        param.put("limitStart", (pageVo.getStartIndex() > -1) ? pageVo.getStartIndex() : 0);
        param.put("limitEnd", StringUtils.isNotBlank(bo.getVin()) ? "1" : pageVo.getPageSize());
        long start = System.currentTimeMillis();
        List<WmsInventoryLocation> wmsInventoryLocations = WmsInboundOrderDetailExtMapper.selectByParam(param, del);
        long time = System.currentTimeMillis() - start;

        if (CollectionUtils.isEmpty(wmsInventoryLocations)) {
            throw new BusinessException("未查询到对应的入库数据");
        }
        //拼接车架号
        if ((StringUtils.isNotBlank(bo.getInboundBegin()) && StringUtils.isNotBlank(bo.getInboundEnd()))
                || StringUtils.isNotBlank(bo.getVin()) || StringUtils.isNotBlank(bo.getOrderno())) {
            sb = new StringBuilder();
            for (WmsInventoryLocation v : wmsInventoryLocations) {
                sb.append(v.getInvlocVin()).append(",");
            }
        }

        //将日期转为字符串供接口调用
        String ob = null;
        String oe = null;
        if (orderBegin != null && orderEnd != null) {
            ob = tms_request.format(orderBegin);
            oe = tms_request.format(orderEnd);
        }
        //车架号拼接--如果只按订单号也可能为空!
        String vinList = null;
        if (StringUtils.isNotBlank(sb)) {
            vinList = sb.substring(0, sb.length() - 1);
        }
        String result = null;
        try {
            result = callTms(StringUtils.isBlank(vinList) ? "" : vinList, StringUtils.isBlank(ob) ? "" : ob, StringUtils.isBlank(oe) ? "" : oe);
            //写入日志记录
            insertTmsLog(ob, oe, vinList, result);
        } catch (Exception e) {
            logger.error("vehicleTracking 调用tms接口失败 error:", e);
            result = "{success:false," +
                    "msg:vehicleTracking 调用tms接口超时失败}";
            insertTmsLog(ob, oe, vinList, result);
            throw new BusinessException("vehicleTracking 调用tms接口失败");
        }
        //解析tms 结果
        if (StringUtils.isBlank(result)) {
            throw new BusinessException("调用tms未返回结果");
        }
        long starttms = System.currentTimeMillis();
        List<WmsVehicleTrackingDTO> wmsVehicleTrackingDTOS = fetchParseTmsResult(result);
        long timetms = System.currentTimeMillis() - starttms;
        //返回结果至页面--查询本地的跟踪表
        //使用map拼接条件
        ResultDTOWithPagination<List<WmsVehicleTrackingWithBLOBs>> withPagination = new ResultDTOWithPagination<>();
        HashMap<String, Object> paramTracking = Maps.newHashMap();
        paramTracking.put("del", DeleteFlagEnum.DELETED.getValue());
        paramTracking.put("vin", bo.getVin());
        paramTracking.put("inboundBegin", inboundBegin);
        paramTracking.put("inboundEnd", inboundEnd);
        paramTracking.put("orderBegin", orderBegin);
        paramTracking.put("orderEnd", orderEnd);
        paramTracking.put("orderno", bo.getOrderno());
        paramTracking.put("orderByCause", StringUtils.isBlank(pageVo.getOrder()) ? " a.inbound_time desc " : pageVo.getOrder());
        paramTracking.put("limitStart", (pageVo.getStartIndex() > -1) ? pageVo.getStartIndex() : 0);
        paramTracking.put("limitEnd", pageVo.getPageSize());
        long startreturn = System.currentTimeMillis();
        List<WmsVehicleTrackingWithBLOBs> wmsVehicleTrackings = wmsVehicleTrackingExtMapper.selectByParam(paramTracking);
        Integer totalRecords = wmsVehicleTrackingExtMapper.selectCountByParam(paramTracking);
        long timeReturn = System.currentTimeMillis() - startreturn;

        withPagination.setData(wmsVehicleTrackings);
        pageVo.setTotalRecord(totalRecords);
        withPagination.setPageVo(pageVo);

        //存入及更新数据库--多线程防止锁表 最后再写入数据库
        ExecutorService service = null;
        try {
            service = Executors.newFixedThreadPool(5);
            service.execute(() -> {
                long startupdate1 = System.currentTimeMillis();
                saveOrUpdateVehicleTracking(
                        wmsInventoryLocations,
                        tms_response,
                        del,
                        wmsVehicleTrackingDTOS,
                        userId);
                long timeupddate1 = System.currentTimeMillis() - startupdate1;
            });
            service.shutdown();
        } catch (Exception e) {
            logger.error("WmsVehicleTrackingServiceImpl.updateTrackingList 更新列表失败 error:", e);
        } finally {
            if (service != null) {
                service.shutdownNow();
            }
        }

        long startupdate = System.currentTimeMillis();
        //saveOrUpdateVehicleTrackingNew(wmsInventoryLocations, tms_response, del, wmsVehicleTrackingDTOS, userId);
        long timeupddate = System.currentTimeMillis() - startupdate;

        return withPagination;
    }

    /**
     * 记录
     *
     * @param ob      订单开始时间
     * @param oe      订单结束时间
     * @param vinList 车架号列表
     * @param result  返回值
     */
    private void insertTmsLog(String ob, String oe, String vinList, String result) {
        Runnable tms = () -> {
            WmsVehicleTrackingLogWithBLOBs log = new WmsVehicleTrackingLogWithBLOBs();
            log.setResultSource("TMS");
            log.setResult(result);
            log.setSendType(WmsGateControllerTypeEnum.GATE_OUT.getCode());
            log.setGmtCreate(new Date());
            log.setUrl(propertyUrl + "/mTerminalOrderStatus.jspx");
            log.setParam("{vinlist:" + (StringUtils.isBlank(vinList) ? "" : vinList) + "," +
                    "orderBegin:" + (StringUtils.isBlank(ob) ? "" : ob) + "," +
                    "orderEnd:" + (StringUtils.isBlank(oe) ? "" : oe) + "}");
            WmsVehicleTrackingLogMapper.insertSelective(log);
        };
        new Thread(tms).start();
    }

    /**
     * 导出excel
     *
     * @param bo       参数封装对象
     * @param request  请求
     * @param response @throws Exception
     */
    @Override
    public void getImportOutExcel(WmsVehicleTrackingBO bo, HttpServletRequest request, HttpServletResponse response) throws Exception {
        logger.info("WmsVehicleTrackingServiceImpl.getImportOutExcel paramTracking{},{},{},{}", bo);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String whCode = bo.getWhCode();
        String userId = bo.getUserId();
        if (StringUtils.isBlank(whCode)) {
            throw new BusinessException("仓库code为空!");
        }
        if (!WhCodeEnum.UNLCN_XN_CQ.getValue().equals(whCode)) {
            throw new BusinessException("该仓库不支持该操作!");
        }
        if (StringUtils.isBlank(userId)) {
            throw new BusinessException("用户Id不能为空!");
        }
        //设置分页
        PageVo pageVo = new PageVo();
        if (StringUtils.isNotBlank(bo.getPageNo())) {
            pageVo.setPageNo(Integer.valueOf(bo.getPageNo()));
        }
        if (StringUtils.isNotBlank(bo.getPageSize())) {
            pageVo.setPageSize(Integer.valueOf(bo.getPageSize()));
        }
        if (StringUtils.isBlank(bo.getVin())
                && StringUtils.isBlank(bo.getOrderno())
                && StringUtils.isBlank(bo.getInboundBegin())
                && StringUtils.isBlank(bo.getInboundEnd())
                && StringUtils.isBlank(bo.getOrderEnd())
                && StringUtils.isBlank(bo.getOrderBegin())) {
            throw new BusinessException("订单号、车架号、下单时间、入库时间，至少填写一项!");
        }

        //校验订单时间和格式
        Date orderBegin = null;
        Date orderEnd = null;
        if (StringUtils.isNotBlank(bo.getOrderBegin())) {
            try {
                orderBegin = sdf.parse(bo.getOrderBegin());
            } catch (ParseException e) {
                throw new BusinessException("订单起始时间格式不正确(yyyy-MM-dd HH:mm:ss)");
            }
        }
        if (StringUtils.isNotBlank(bo.getOrderEnd())) {
            try {
                orderEnd = sdf.parse(bo.getOrderEnd());
            } catch (ParseException e) {
                throw new BusinessException("订单起始时间格式不正确(yyyy-MM-dd HH:mm:ss)");
            }
        }

        byte del = DeleteFlagEnum.DELETED.getValue();
        //1.先查询本地介于入库时间开始和入库
        Date inboundBegin = null;
        if (StringUtils.isNotBlank(bo.getInboundBegin())) {
            inboundBegin = sdf.parse(bo.getInboundBegin());
        }
        Date inboundEnd = null;
        if (StringUtils.isNotBlank(bo.getInboundEnd())) {
            inboundEnd = sdf.parse(bo.getInboundEnd());
        }
        //下单时间不为空--入库时间--车架号--订单号
        //使用map拼接条件
        HashMap<String, Object> param = Maps.newHashMap();
        param.put("vin", bo.getVin());
        param.put("inboundBegin", inboundBegin);
        param.put("inboundEnd", inboundEnd);
        param.put("orderno", bo.getOrderno());
        param.put("orderByCause", StringUtils.isBlank(pageVo.getOrder()) ? " a.gmt_create desc " : pageVo.getOrder());
        param.put("limitStart", (pageVo.getStartIndex() > -1) ? pageVo.getStartIndex() : 0);
        param.put("limitEnd", pageVo.getPageSize());
        List<WmsInventoryLocation> wmsInventoryLocations = WmsInboundOrderDetailExtMapper.selectByParam(param, del);
        if (CollectionUtils.isEmpty(wmsInventoryLocations)) {
            throw new BusinessException("未查询到对应的入库数据");
        }

        //使用map拼接条件 --查询本地的跟踪记录表
        HashMap<String, Object> paramTracking = Maps.newHashMap();
        paramTracking.put("del", DeleteFlagEnum.DELETED.getValue());
        paramTracking.put("vin", bo.getVin());
        paramTracking.put("inboundBegin", inboundBegin);
        paramTracking.put("inboundEnd", inboundEnd);
        paramTracking.put("orderBegin", orderBegin);
        paramTracking.put("orderEnd", orderEnd);
        paramTracking.put("orderno", bo.getOrderno());
        paramTracking.put("orderByCause", StringUtils.isBlank(pageVo.getOrder()) ? " a.inbound_time desc " : pageVo.getOrder());
        paramTracking.put("limitStart", (pageVo.getStartIndex() > -1) ? pageVo.getStartIndex() : 0);
        paramTracking.put("limitEnd", pageVo.getPageSize());
        List<WmsVehicleTrackingWithBLOBs> wmsVehicleTrackings = wmsVehicleTrackingExtMapper.selectAllRecords(paramTracking);
        if (CollectionUtils.isEmpty(wmsVehicleTrackings)) {
            throw new BusinessException("未查询到对应的跟踪数据");
        }
        //查询本地库中,返回数据并生成excel
        //1.导出excel头
        String[] hearders = new String[]{
                "底盘号", "入前置库时间", "收车异常信息", "下单时间\n" + "（SAP备料）", "调度时间", "备料出库时间", "SAP发运时间", "装车确认时间",
                "发运时间\n" + "（关联道闸开启）", "承运商", "司机", "联系方式", "运抵时间", "SAP收车时间", "目的地", "经销商", "经销商联系方式", "出库异常信息"
        };
        //设置值
        HSSFWorkbook workbook = updateExportToExcel("信息跟踪导出数据", hearders, wmsVehicleTrackings, "yyyy-MM-dd HH:mm:ss");
        //输出流--提供下载
        BufferedOutputStream bufferedOutputStream = null;
        BufferedInputStream bufferedInputStream = null;
        ByteArrayOutputStream bos = null;
        ByteArrayInputStream inputStream = null;
        ServletOutputStream outputStream = null;
        try {
            bos = new ByteArrayOutputStream(2048);
            workbook.write(bos);
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-disposition", BrowerEncodeingUtils.getContentDisposition("ImportTrackingTemplate.xls", request));
            inputStream = new ByteArrayInputStream(bos.toByteArray());
            outputStream = response.getOutputStream();
            bufferedOutputStream = new BufferedOutputStream(outputStream);
            bufferedInputStream = new BufferedInputStream(inputStream);
            byte[] bytes = new byte[2048];
            int len;
            while ((len = inputStream.read(bytes)) != -1) {
                outputStream.write(bytes, 0, len);
            }
        } catch (IOException e) {
            logger.error("WmsVehicleTrackingServiceImpl.getImportOutExcel paramTracking", e);
            throw new BusinessException("下载文件异常!");
        } finally {
            if (bufferedInputStream != null) {
                bufferedInputStream.close();
            }
            if (bufferedOutputStream != null) {
                bufferedOutputStream.close();
            }
            if (inputStream != null) {
                inputStream.close();
            }
            if (outputStream != null) {
                outputStream.close();
            }
            if (bos != null) {
                bos.close();
            }
            if (workbook != null) {
                workbook.close();
            }
        }
    }


    /**
     * @param title   sheet标题
     * @param headers 表头
     * @param pattern 时间格式
     */
    private HSSFWorkbook updateExportToExcel(String title, String[] headers,
                                             List<WmsVehicleTrackingWithBLOBs> wmsVehicleTrackings, String pattern) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.WHITE.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style2.setFont(font2);

        //// 声明一个画图的顶级管理器
        //HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        //// 定义注释的大小和位置,详见文档
        //HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
        //        0, 0, 0, (short) 4, 2, (short) 6, 5));
        //// 设置注释内容
        //comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
        //// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        //comment.setAuthor("leno");

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        // 遍历集合数据，产生数据行
        Iterator<WmsVehicleTrackingWithBLOBs> iterator = wmsVehicleTrackings.iterator();
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        int index = 0;
        while (iterator.hasNext()) {
            index++;
            row = sheet.createRow(index);//创建行
            WmsVehicleTrackingWithBLOBs next = iterator.next();
            //对应头  设置cell的值
            for (short i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                //"底盘号", "入前置库时间", "收车异常信息", "下单时间\n" + "（SAP备料）", "调度时间", "备料出库时间",
                // "SAP发运时间", "装车确认时间", "发运时间\n" + "（关联道闸开启）", "承运商", "司机", "联系方式", "运抵时间",
                //  "SAP收车时间", "目的地", "经销商", "经销商联系方式", "出库异常信息"
                switch (i) {
                    case 0: {
                        cell.setCellValue(updateSetCellValue(next.getVin()));
                        break;
                    }
                    case 1: {
                        cell.setCellValue(updateSetCellValue(getFormatDate(next.getInboundTime())));
                        break;
                    }
                    case 2: {
                        cell.setCellValue(updateSetCellValue(next.getReceiveExcpDesc()));
                        break;
                    }
                    case 3: {
                        cell.setCellValue(updateSetCellValue(getFormatDate(next.getOrderTime())));
                        break;
                    }
                    case 4: {
                        cell.setCellValue(updateSetCellValue(getFormatDate(next.getShipTime())));
                        break;
                    }
                    case 5: {
                        cell.setCellValue(updateSetCellValue(getFormatDate(next.getOutboundTime())));
                        break;
                    }
                    case 6: {
                        cell.setCellValue(updateSetCellValue(getFormatDate(next.getShipmentTime())));
                        break;
                    }
                    case 7: {
                        cell.setCellValue(updateSetCellValue(getFormatDate(next.getLoadingConfirmTime())));
                        break;
                    }
                    case 8: {
                        cell.setCellValue(updateSetCellValue(getFormatDate(next.getShipmentGateTime())));
                        break;
                    }
                    case 9: {
                        cell.setCellValue(updateSetCellValue(next.getCarrier()));
                        break;
                    }
                    case 10: {
                        cell.setCellValue(updateSetCellValue(next.getDriver()));
                        break;
                    }
                    case 11: {
                        cell.setCellValue(updateSetCellValue(next.getMobile()));
                        break;
                    }
                    case 12: {
                        cell.setCellValue(updateSetCellValue(getFormatDate(next.getArrivedTime())));
                        break;
                    }
                    case 13: {
                        cell.setCellValue(updateSetCellValue(getFormatDate(next.getPickTime())));
                        break;
                    }
                    case 14: {
                        cell.setCellValue(updateSetCellValue(next.getRouteEnd()));
                        break;
                    }
                    case 15: {
                        cell.setCellValue(updateSetCellValue(next.getDealer()));
                        break;
                    }
                    case 16: {
                        cell.setCellValue(updateSetCellValue(next.getDealerMobile()));
                        break;
                    }
                    case 17: {
                        cell.setCellValue(updateSetCellValue(next.getShipExcpDesc()));
                        break;
                    }
                    default:
                        break;
                }
            }
        }
        return workbook;
    }

    /**
     * 判断字符串设置值--非空过滤
     *
     * @return
     */
    private String updateSetCellValue(String value) {
        if (StringUtils.isNotBlank(value)) {
            return value;
        }
        return "";
    }

    /**
     * 时间格式化--非空过滤
     *
     * @param date
     * @return
     */
    private String getFormatDate(Date date) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        if (date != null) {
            return sdf.format(date);
        }
        return "";
    }

    /**
     * 插入或者更新数据库
     *
     * @param wmsInventoryLocations  库存库位列表
     * @param sdfTms                 tms时间格式
     * @param del                    逻辑删除
     * @param wmsVehicleTrackingDTOS 传输参数对象
     * @param userId                 用户id
     */
    private void saveOrUpdateVehicleTrackingNew(List<WmsInventoryLocation> wmsInventoryLocations, SimpleDateFormat sdfTms, byte del, List<WmsVehicleTrackingDTO> wmsVehicleTrackingDTOS, String userId) {

        if (CollectionUtils.isNotEmpty(wmsVehicleTrackingDTOS)) {
            //存入数据库
            wmsVehicleTrackingDTOS.forEach((WmsVehicleTrackingDTO v) -> {
                //进行重复性校验,重复就更新数据--写到存储过程
                WmsVehicleTrackingWithBLOBs tracking = new WmsVehicleTrackingWithBLOBs();
                tracking.setOrderno(v.getOrderno());//订单号
                tracking.setCustshipno(v.getCustshipno());//运单号
                tracking.setCarrier(v.getSupplier());
                tracking.setDriver(v.getDriver());
                tracking.setVin(v.getVin());
                tracking.setGmtCreate(new Date());
                tracking.setGmtUpdate(new Date());
                tracking.setModifyUserId(Integer.valueOf(userId));
                tracking.setCreateUserId(Integer.valueOf(userId));
                tracking.setIsDeleted(DeleteFlagEnum.NORMAL.getValue());
                tracking.setMobile(v.getMobile());
                tracking.setWhCode(WhCodeEnum.UNLCN_XN_CQ.getValue());
                tracking.setWhName(WhCodeEnum.UNLCN_XN_CQ.getText());
                //设置入库时间
                for (WmsInventoryLocation location : wmsInventoryLocations) {
                    String invlocVin = location.getInvlocVin();
                    if (StringUtils.isNotBlank(invlocVin) && invlocVin.equals(v.getVin())) {
                        tracking.setInboundTime(location.getGmtCreate());
                    }
                }
                //设置出库时间
                try {
                    getOutboundTime(del, v, tracking);
                } catch (Exception e) {
                    logger.error("vehicle 订单跟踪更新出库时间异常 error:", e);
                    throw new BusinessException("vehicle 订单跟踪更新出库时间异常");
                }

                updateSetDataFromTms(sdfTms, v, tracking);

                wmsVehicleTrackingExtMapper.insertOrUpdate(tracking);
            });
        }
    }

    @Autowired
    private WmsVehicleTrackingLogMapper WmsVehicleTrackingLogMapper;

    /**
     * 插入或者更新数据库
     *
     * @param wmsInventoryLocations  库存列表
     * @param sdfTms                 时间格式
     * @param del                    逻辑删除
     * @param wmsVehicleTrackingDTOS 查询的发运跟踪数据对象
     * @param userId                 用户id
     */
    private void saveOrUpdateVehicleTracking(List<WmsInventoryLocation> wmsInventoryLocations,
                                             SimpleDateFormat sdfTms,
                                             byte del,
                                             List<WmsVehicleTrackingDTO> wmsVehicleTrackingDTOS,
                                             String userId) {

        if (CollectionUtils.isNotEmpty(wmsVehicleTrackingDTOS)) {
            //存入数据库
            wmsVehicleTrackingDTOS.forEach((WmsVehicleTrackingDTO v) -> {
                //进行重复性校验,重复就更新数据
                WmsVehicleTrackingExample example = new WmsVehicleTrackingExample();
                example.createCriteria().andVinEqualTo(v.getVin())
                        .andIsDeletedNotEqualTo(del);
                List<WmsVehicleTrackingWithBLOBs> wmsVehicleTrackings = wmsVehicleTrackingMapper.selectByExampleWithBLOBs(example);
                if (CollectionUtils.isNotEmpty(wmsVehicleTrackings)) {
                    WmsVehicleTrackingWithBLOBs tracking = wmsVehicleTrackings.get(0);
                    if (!String.valueOf(SendBusinessFlagEnum.SEND_Y.getValue()).equals(tracking.getDealStatus())) {
                        //更新--未完成

                        tracking.setOrderno(v.getOrderno());//订单号
                        tracking.setCustshipno(v.getCustshipno());//运单号
                        tracking.setCarrier(v.getSupplier());
                        tracking.setDriver(v.getDriver());
                        tracking.setVin(v.getVin());
                        tracking.setGmtUpdate(new Date());
                        tracking.setModifyUserId(Integer.valueOf(userId));
                        tracking.setMobile(v.getMobile());
                        updateSetDataFromTms(sdfTms, v, tracking);
                        //设置入库时间
                        for (WmsInventoryLocation location : wmsInventoryLocations) {
                            String invlocVin = location.getInvlocVin();
                            if (StringUtils.isNotBlank(invlocVin) && invlocVin.equals(v.getVin())) {
                                tracking.setInboundTime(location.getGmtCreate());
                            }
                        }
                        //设置出库时间
                        try {
                            getOutboundTime(del, v, tracking);
                        } catch (Exception e) {
                            logger.error("vehicle 订单跟踪更新出库时间异常 error:", e);
                            throw new BusinessException("vehicle 订单跟踪更新出库时间异常");
                        }
                        wmsVehicleTrackingMapper.updateByPrimaryKeySelective(tracking);
                    }
                } else {
                    //新增
                    WmsVehicleTrackingWithBLOBs tracking = new WmsVehicleTrackingWithBLOBs();
                    tracking.setOrderno(v.getOrderno());//订单号
                    tracking.setCustshipno(v.getCustshipno());//运单号
                    tracking.setCarrier(v.getSupplier());
                    tracking.setDriver(v.getDriver());
                    tracking.setVin(v.getVin());
                    tracking.setGmtCreate(new Date());
                    tracking.setGmtUpdate(new Date());
                    tracking.setModifyUserId(Integer.valueOf(userId));
                    tracking.setCreateUserId(Integer.valueOf(userId));
                    tracking.setIsDeleted(DeleteFlagEnum.NORMAL.getValue());
                    tracking.setMobile(v.getMobile());
                    tracking.setDealerMobile(v.getDealer_mobile());
                    tracking.setWhCode(WhCodeEnum.UNLCN_XN_CQ.getValue());
                    tracking.setWhName(WhCodeEnum.UNLCN_XN_CQ.getText());
                    //设置tms时间数据
                    updateSetDataFromTms(sdfTms, v, tracking);
                    //设置入库时间
                    for (WmsInventoryLocation location : wmsInventoryLocations) {
                        String invlocVin = location.getInvlocVin();
                        if (StringUtils.isNotBlank(invlocVin) && invlocVin.equals(v.getVin())) {
                            tracking.setInboundTime(location.getGmtCreate());
                        }
                    }
                    //设置出库时间 和末端发运异常信息
                    try {
                        getOutboundTime(del, v, tracking);
                    } catch (Exception e) {
                        logger.error("vehicle 订单跟踪更新出库时间异常 error:", e);
                        throw new BusinessException("vehicle 订单跟踪更新出库时间异常");
                    }
                    //新增数据的时查询wms收货异常信息
                    updateOrInsertReceiveExcpDetail(v, tracking);


                    wmsVehicleTrackingMapper.insertSelective(tracking);
                }
            });
        }

    }

    private void updateOrInsertReceiveExcpDetail(WmsVehicleTrackingDTO v, WmsVehicleTrackingWithBLOBs tracking) {
        //设置收货异常信息--只在新增的时候执行调用wms-api的接口
        String result = null;
        try {
            result = callWms(v.getVin());
            //记录wms日志
            insertWmsLog(v, result);
        } catch (Exception e) {
            logger.error("vehicleTracking 调用WMS接口失败 error:", e);
            result = "{success:false," +
                    "msg:vehicleTracking 调用WMS接口超时失败}";
            insertWmsLog(v, result);
            //throw new BusinessException("访问WMS接口失败");
        }
        if (StringUtils.isNotBlank(result)) {
            JSONObject json = JSONObject.parseObject(result);
            Boolean success = json.getBoolean("success");
            if (success) {
                //只处理成功的数据
                String data = json.getString("data");
                List<WmsSelectExcpDetailsByVinDTO> details = JSONObject.parseArray(data, WmsSelectExcpDetailsByVinDTO.class);
                if (CollectionUtils.isNotEmpty(details)) {
                    StringBuilder excpDe = new StringBuilder();
                    details.stream()
                            .filter(detail -> !Objects.equals(detail, null) && StringUtils.isNotBlank(detail.getHurt()))
                            .forEach(detail -> excpDe.append(detail.getBody())
                                    .append("_")
                                    .append(detail.getHurt())
                                    .append(","));
                    String detail = excpDe.substring(0, excpDe.length() - 1);
                    if (StringUtils.isNotBlank(detail)) {
                        tracking.setReceiveExcpDesc(StringUtils.isBlank(detail) ? "收车合格" : detail);
                    }
                }
                String inspectTime = details.get(0).getInspectTime();
                if (StringUtils.isNotBlank(inspectTime)) {
                    Date iT = DateUtils.StrToDate(inspectTime, DateUtils.FORMAT_DATETIME);
                    tracking.setReceiveExcpInspecttime(iT);
                }
            }
        }
    }

    /**
     * 插入wms系统接口调用日志
     *
     * @param v      参数传输对象
     * @param result 返回值
     */
    private void insertWmsLog(WmsVehicleTrackingDTO v, String result) {
        Runnable wms = () -> {
            JSONObject json = JSONObject.parseObject(result);
            WmsVehicleTrackingLogWithBLOBs log = new WmsVehicleTrackingLogWithBLOBs();
            log.setParam(v.getVin());
            log.setUrl(wmsApiUrl + "/getExcpFromWms");
            log.setResult(result);
            log.setGmtCreate(new Date());
            log.setSendType(WmsGateControllerTypeEnum.GATE_OUT.getCode());
            log.setResultSource("WMS");
            WmsVehicleTrackingLogMapper.insertSelective(log);
        };
        new Thread(wms).start();
    }

    /**
     * 设置tms数据至本地
     *
     * @param sdfTms   tms时间格式
     * @param v        参数对象
     * @param tracking 查询的或者要新增的本地对象
     */
    private void updateSetDataFromTms(SimpleDateFormat sdfTms, WmsVehicleTrackingDTO v, WmsVehicleTrackingWithBLOBs tracking) {
        if (StringUtils.isNotBlank(v.getAssign_date())) {
            try {
                tracking.setShipTime(sdfTms.parse(v.getAssign_date()));//调度时间
            } catch (ParseException e) {
                throw new BusinessException("TMS接口返回调度时间格式错误");
            }

        }
        if (StringUtils.isNotBlank(v.getOrder_date())) {
            try {
                tracking.setOrderTime(sdfTms.parse(v.getOrder_date()));//
            } catch (ParseException e) {
                throw new BusinessException("TMS接口返回下单时间格式错误");
            }

        }
        if (StringUtils.isNotBlank(v.getSap_out_date())) {
            try {
                tracking.setShipmentTime(sdfTms.parse(v.getSap_out_date()));//发运时间
            } catch (ParseException e) {
                throw new BusinessException("TMS接口返回发运时间格式错误");
            }

        }
        if (StringUtils.isNotBlank(v.getErp_load_date())) {
            try {
                tracking.setLoadingConfirmTime(sdfTms.parse(v.getErp_load_date()));
            } catch (ParseException e) {
                throw new BusinessException("TMS接口返回装车确认时间格式错误");
            }

        }
        if (StringUtils.isNotBlank(v.getErp_out_date())) {
            try {
                tracking.setShipmentGateTime(sdfTms.parse(v.getErp_out_date()));//erp发运时间
            } catch (ParseException e) {
                throw new BusinessException("TMS接口返回ERP发运时间格式错误");
            }

        }
        if (StringUtils.isNotBlank(v.getErp_arrival_date())) {
            try {
                tracking.setArrivedTime(sdfTms.parse(v.getErp_arrival_date()));
            } catch (ParseException e) {
                throw new BusinessException("TMS接口返回ERP运抵时间格式错误");
            }
        }
        if (StringUtils.isNotBlank(v.getSap_receive_date())) {
            try {
                tracking.setPickTime(sdfTms.parse(v.getSap_receive_date()));
                tracking.setDealStatus(String.valueOf(SendBusinessFlagEnum.SEND_Y.getValue()));
            } catch (ParseException e) {
                throw new BusinessException("TMS接口返回SAP收车时间格式错误");
            }
        }
        //经销商
        if (StringUtils.isNotBlank(v.getDealer())) {
            tracking.setDealer(v.getDealer());
        }
        if (StringUtils.isNotBlank(v.getDealer_mobile())) {
            tracking.setDealerMobile(v.getDealer_mobile());
        }
    }


    /**
     * 获取出库的时间
     *
     * @param del      逻辑删除
     * @param v        参数对象
     * @param tracking 订单跟踪对象
     */
    private void getOutboundTime(byte del, WmsVehicleTrackingDTO v, WmsVehicleTrackingWithBLOBs tracking) throws Exception {
        //设置出库时间--对应任务完成时间
        WmsOutboundTaskExample taskExample = new WmsOutboundTaskExample();
        taskExample.setOrderByClause("ot_id desc");
        taskExample.or().andOtVinEqualTo(v.getVin())
                .andOtQuitFlagIsNull()
                .andOtStatusNotEqualTo(String.valueOf(WmsOutboundTaskStatusEnum.WMS_OUTBOUND_TASK_CANCLE.getValue()))
                .andIsDeletedNotEqualTo(del);
        taskExample.or().andOtVinEqualTo(v.getVin())
                .andOtQuitFlagNotEqualTo(TaskQuitFlagEnum.HAS_QUIT.getValue())
                .andOtStatusNotEqualTo(String.valueOf(WmsOutboundTaskStatusEnum.WMS_OUTBOUND_TASK_CANCLE.getValue()))
                .andIsDeletedNotEqualTo(del);
        List<WmsOutboundTask> wmsOutboundTasks = wmsOutboundTaskMapper.selectByExample(taskExample);
        if (CollectionUtils.isNotEmpty(wmsOutboundTasks)) {
            WmsOutboundTask outboundTask = wmsOutboundTasks.get(0);
            tracking.setOutboundTime(outboundTask.getOtTaskEndTime());
            tracking.setRouteEnd(outboundTask.getOtDest());//设置目的地
            //看是否存在异常信息
            if (WmsOutboundInspectStatusEnum.WAYBILL_DAMAGE.getValue() == (outboundTask.getInspectStatus())) {
                //带伤发运的数据  需要再设置末端发运异常信息
                //Date parse = null;
                //try {
                //    parse = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("2017-12-20 00:00:00");
                //} catch (ParseException e) {
                //    logger.error("订单跟踪vehicleTracking 日期转换异常 error:", e);
                //    throw new BusinessException("订单跟踪vehicleTracking 日期转换异常");
                //}
                String missComponentNames = wmsOutboundComponentMissService.getMissComponentNames(outboundTask.getOtVin(), WhCodeEnum.UNLCN_XN_CQ.getValue());
                HashMap<String, Object> paramMap = Maps.newHashMap();
                paramMap.put("notDel", DeleteFlagEnum.NORMAL.getValue());
                paramMap.put("inspectId", outboundTask.getOtId());
                paramMap.put("whCode", WhCodeEnum.UNLCN_XN_CQ.getValue());
                paramMap.put("notCancle", WmsOutboundTaskStatusEnum.WMS_OUTBOUND_TASK_CANCLE.getValue());
                List<WmsVehicleTrackingExcpDTO> list = wmsOutboundInspectExpandMapper.selectExcpListByInspectId(paramMap);
                StringBuilder excpDetail = new StringBuilder();
                if (CollectionUtils.isNotEmpty(list)) {
                    list.stream().filter((WmsVehicleTrackingExcpDTO item) -> !Objects.equals(item, null))
                            .filter(item -> StringUtils.isNotBlank(item.getHurt()))
                            .forEach(item -> excpDetail.
                                    append(item.getBody())
                                    .append("_")
                                    .append(item.getHurt())
                                    .append(","));
                }
                StringBuilder excp = new StringBuilder();
                if (StringUtils.isNotBlank(excpDetail)) {
                    excp.append("质损异常:").append(excpDetail.substring(0, excpDetail.length() - 1));
                }
                if (StringUtils.isNotBlank(missComponentNames)) {
                    excp.append("----缺件异常:").append(missComponentNames);
                }
                tracking.setShipExcpDesc(StringUtils.isBlank(excp.toString()) ? "合格发运" : excp.toString());
            }
        }

    }

    /**
     * 解析tms结果
     *
     * @param result 返回结果
     * @return 解析的结果
     */
    private List<WmsVehicleTrackingDTO> fetchParseTmsResult(String result) {

        JSONObject jsonObject = JSONObject.parseObject(result);
        Boolean success = jsonObject.getBoolean("success");
        String message = jsonObject.getString("message");
        String records = jsonObject.getString("records");
        if (!success) {
            throw new BusinessException("TMS接口返回错误信息:" + message);
        }
        return JSONObject.parseArray(records, WmsVehicleTrackingDTO.class);
    }


    @Value("${tms.pickup.host.timeoutfortracking}")
    private String propertyTimeForTracking;

    /**
     * 调用tms的接口(跟踪时间段内的车辆情况)
     *
     * @param vinList          车架号列表  用逗号拼接
     * @param order_date_begin 下单开始时间
     * @param order_date_end   下单结束时间
     * @return 返回值
     */
    private String callTms(String vinList, String order_date_begin, String order_date_end) {
        // 构建URL
        String url = propertyUrl;
        //final int POPERTY_TIME = 30000;
        Integer time = Integer.parseInt(propertyTimeForTracking);
        String encode_key = propertyKey;
        Map<String, Object> map = new HashMap<>();
        Map<String, Object> headerMap = new HashMap<>();
        headerMap.put("encode-key", encode_key);
        url = url + "/mTerminalOrderStatus.jspx";
        map.put("vin_list", vinList);
        map.put("order_date_begin", order_date_begin);
        map.put("order_date_end", order_date_end);//出库时间
        String result = null;
        try {
            result = HttpRequestUtil.sendHttpPost(url, headerMap, map, time);
        } catch (Exception e) {
            throw new BusinessException("访问tms接口失败");
        }
        return result;
    }


    /**
     * 查询车辆对应的收货异常信息
     *
     * @param vin 车架号
     * @return 返回值
     */
    private String callWms(String vin) {
        // 构建URL
        String url = wmsApiUrl;
        Integer time = Integer.parseInt(propertyTimeForTracking);
        String encode_key = propertyKey;
        Map<String, Object> map = new HashMap<>();
        Map<String, Object> headerMap = new HashMap<>();
        headerMap.put("encode-key", encode_key);
        url = url + "/getExcpFromWms";
        map.put("vin", vin);
        String result = null;
        try {
            result = HttpRequestUtil.sendHttpPost(url, headerMap, map, time);
        } catch (Exception e) {
            throw new BusinessException("访问wms接口失败");
        }
        return result;
    }

}
